/**
 * Created by wry on 2015/4/13.
 */
var Users = mongoose.model('user');
var async = require('async');
var neighbourActivity = mongoose.model('activity_neighbour');
var mysqlUtil = require('../../common/utils/mysqlUtil');
var Question = mongoose.model('question');
var Community = mongoose.model('community');

Date.prototype.format = function (format) {
    var o = {
        "M+": this.getMonth() + 1, //month
        "d+": this.getDate(), //day
        "m+": this.getMinutes(), //minute
        "s+": this.getSeconds(), //second
        "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
        "S": this.getMilliseconds() //millisecond
    }
    if (/(y+)/.test(format)) format = format.replace(RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o) if (new RegExp("(" + k + ")").test(format))
        format = format.replace(RegExp.$1,
            RegExp.$1.length == 1 ? o[k] :
                ("00" + o[k]).substr(("" + o[k]).length));
    return format;
}
if (!String.prototype.format) {
    String.prototype.format = function () {
        var args = arguments;
        return this.replace(/{(\d+)}/g, function (match, number) {
            return typeof args[number] != 'undefined'
                ? args[number]
                : match
                ;
        });
    };
}
function commentToString(f) {
    return f.toString().
        replace(/^[^\/]+\/\*!?/, '').
        replace(/\*\/[^\/]+$/, '');
}
function sqlConditionString(conditionList) {
    var len = conditionList.length
    if (len == 0)
        return ''
    var condition = "where "

    for (var i = 0; i < len; i++) {
        condition += conditionList[i]
        if (i != len - 1)
            condition += ' and '
    }
    return condition
}


exports.getMarketingInviteListService = function (community, callback) {
    var commCondition = ''
    if (community != 'all')
        commCondition = "and u2.community ='" + community + "'"

    var sql = commentToString(function () {
        /*
         select u2.nickName inviter, u2._id 'inviterId', u2.community 'inviterComm', u1.nickName invitee, u1._id 'inviteeId', u1.community 'inviteeComm', convert(date(u1.dateCreated), char) 'dateCreated'
         from {0}.userAll u1, {0}.userAll u2
         where u1.invitedBy = u2._id
         {1}
         order by u1.dateCreated desc
         */
    }).format('66xiaoqu_rec', commCondition)

    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getMarketingInviteSentListService = function (community, callback) {
    var commCondition = ''
    if (community != 'all')
        commCondition = "and community ='" + community + "'"

    var sql = commentToString(function () {
        /*

         select community, communityName, convert(dc, char) dc, uid, nickName, sum(iShared) invitationShared, sum(iSuccess) invitationSuccessful from
         (
         select community, date(mob.dateCreated) dc, u._id uid, nickName, count(*) iShared, 0 'iSuccess'
         from mobile_app_user_share_restful mob, user u
         where mob.userId = u._id
         and feedsType = 'app'
         and mob.dateCreated is not null
         group by uid, dc
         union
         select u2.community, date(u.dateCreated) dc, u2._id uid, u2.nickName, 0 'iShared', count(*) iSuccess
         from user u, user u2
         where u.invitedby = u2._id
         and u.invitedBy is not null
         -- and u.invitedby = '56221619de1ce55a72a18ce4'
         and u.dateCreated is not null
         group by uid, dc
         ) alll, community c
         where community = c._id
         {0}
         group by uid, dc

         */
    }).format(commCondition)

    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getCommAppsService = function (community, startDate, endDate, callback) {

    var commCondition = ""
    var startDateCondition = ""
    var endDateCondition = ""

    var conditionList = []

    if (community != 'all')
        conditionList.push("comm._id = '{0}'".format(community))
    if (startDate != 'all')
        conditionList.push("commApps.dateCreated >='{0}'".format(startDate))
    if (endDate != 'all')
        conditionList.push("commApps.dateCreated <='{0}'".format(endDate))

    var conditionString = sqlConditionString(conditionList)

    var sql = commentToString(function () {
        /*
         SELECT
         commApps._id commAppId, convert(commApps.dateCreated, char) dateCreated, user, nickName, phone, comm._id commId, commApps.communityName, note
         FROM
         66xiaoqu_rec.community_applications commApps left join 66xiaoqu_rec.community comm on commApps.communityName = comm.communityName
         left join 66xiaoqu_rec.user user
         on commApps.user = user._id
         {0}
         */
    }).format(conditionString)
    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
}

exports.getPromoCodeStatsService = function (community, startDate, endDate, callback) {

    var commCondition = ""
    var startDateCondition = ""
    var endDateCondition = ""
    if (community != 'all')
        commCondition = "and user.community = '{0}'".format(community)
    if (startDate != 'all')
        startDateCondition = "and user.dateCreated >='{0}'".format(startDate)
    if (endDate != 'all')
        endDateCondition = "and user.dateCreated <='{0}'".format(endDate)

    var sqlTemp = commentToString(function () {
        /*
         select convert(date(user.dateCreated), char) dc, authenticationStatus auth, count(distinct user._id) count from
         user
         left join
         (
         select * from user_authentications group by user having dateCreated = max(dateCreated)
         ) as auth
         on user._id = auth.user
         where (mobilePhone not like '400%' and mobilePhone not like '500%' and mobilePhone not like '600%' and mobilePhone not like '700%' or mobilePhone is null)
         and promotionCode is {0} null
         and invitedBy is {1} null
         {2}
         {3}
         {4}
         group by dc, authenticationStatus
         */
    }).format('{0}', '{1}', commCondition, startDateCondition, endDateCondition)

    async.series([
        function (callbackInner) {
            var sql = sqlTemp.format('', '')
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result)
            })
        },
        function (callbackInner) {
            var sql = sqlTemp.format('', 'not')
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result)
            })
        },
        function (callbackInner) {
            var sql = sqlTemp.format('not', '')
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result)
            })
        },
        function (callbackInner) {
            var sql = sqlTemp.format('not', 'not')
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result)
            })
        }
    ], callback);

}
